package com.hkts.reportform.datastatics.daoImpl;

import com.hkts.reportform.datastatics.dao.ReportDao;
import com.hkts.reportform.datastatics.entity.ReportForm;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassNamereportDaoImpl
 * @Description
 * @Author yck
 * @Date2020-10-19 11:22
 * @Version V1.0
 **/
@Repository("reportDaoImpl")
public class ReportDaoImpl implements ReportDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DataSource dataSource;
    @Autowired
    private NamedParameterJdbcTemplate temple;

    @Override
    public List<Map<String, Object>> selectInventoryClassBycInvCCode(String cInvCCode) {
     String sql = "select * from InventoryClass where cInvCCode=:cInvCCode ";
        Map<String, Object> map = new HashMap<>();
        map.put("cInvCCode", cInvCCode);
        List<Map<String, Object>> list = temple.queryForList(sql, map);
        return list;
    }

    @Override
    public List<Map<String, Object>> selectInventoryByCInvCCode(String cInvCCode) {
        String sql = "select * from Inventory where cInvCCode=:cInvCCode";
        Map<String, Object> map = new HashMap<>();
        map.put("cInvCCode", cInvCCode);
        List<Map<String, Object>> list = temple.queryForList(sql, map);
        return list;
    }

    @Override
    public List<Map<String, Object>> selectInventoryByCInvCode(String cInvCode) {
        String sql = "select * from Inventory where cInvCode=:cInvCode";
        Map<String, Object> map = new HashMap<>();
        map.put("cInvCode", cInvCode);
        List<Map<String, Object>> list = temple.queryForList(sql, map);
        return list;
    }

    @Override
    public List<Map<String, Object>> selectInventory() {
        String sql = "select * from InventoryClass where iInvCGrade='2' ";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);

        return maps;
    }

    @Override
    public List<Map<String, Object>> auxiliaryMaterialslist() {
        String sql="select cInvCCode from InventoryClass where ( cInvCCode like '04%' or cInvCCode like '05%' or cInvCCode like '06%' or cInvCCode like '07%' or cInvCCode like '08%' or cInvCCode like '09%') and iInvCgrade=2";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public List<ReportForm> getStoredProcedure(String beginDate, String endDate, String inventoryClassStr) throws Exception {
        List<ReportForm> list = new ArrayList<ReportForm>();
        Map<String, Object> stringObjectHashMap = new HashMap<>();
        Connection connection = dataSource.getConnection();

        String storedProc = "{call hbyb_scbb_202008(?,?,?)}";
        try {
            // 建立数据库的连接
            CallableStatement cstmt = connection.prepareCall(storedProc);
            cstmt.setString(1, beginDate);
            cstmt.setString(2, endDate);
            cstmt.setString(3, inventoryClassStr);
            // 查询
            ResultSet rs = cstmt.executeQuery();
            Float rate=(float)0;
            while (rs.next()) {
                if(rs.getFloat(3)!=0){
                    rate= rs.getFloat(4)/rs.getFloat(3);

                }else{
                    rate=(float)0;
                }
                ReportForm form = new ReportForm(rs.getString(1),
                        rs.getString(2),
                        rs.getFloat(3),
                        rs.getFloat(4),
                        rate,
                        rs.getFloat(5),
                        rs.getFloat(6),
                        rs.getFloat(7),
                        rs.getFloat(8),
                        rs.getFloat(9),
                        rs.getFloat(10),
                        rs.getFloat(11)
                );
                list.add(form);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            connection.close();
        }
        return list;

    }
}
